﻿Option Explicit On

Imports System.Text

Public Class Products

    Public Structure ProductInfo
        Public ProductId As Integer
        Public ProductName As String
        Public ProductTypeId As Integer
        Public IndefiniteLeave As Boolean
        Public UKVisaId As Integer
        Public UKVisaName As String
        Public DestinationId As Integer
        Public StageDiagramId As Integer
        Public Weblink As String
        Public ChargeGroupId As Integer
        Public EventGroupId As Integer
        Public StockLetterSetId As Integer
        Public DateCategoryId As Integer
        Public MFBGroupId As Integer
        Public WebLoopId As Integer
        Public FastTrackId As Integer
        Public TargetGroupId As Integer
        Public CalcSetId As Integer
        Public BranchID As Integer
    End Structure

    Public Shared Function GetProductType(ByVal ProductId As Integer) As Integer
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLScalar("Select Product_Type from L_Products Where Product_ID = " & ProductId)
        Catch ex As Exception
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function GetProductInfo(ByVal ProductId As Integer) As ProductInfo


        'Me.Combo_DTCat = Rst.Fields("Product_CalcSet")
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim myData As DataTable

            GetProductInfo = Nothing

            With SQL
                '.Append("select product_id,product_name,product_type,product_indefinite,product_ukvisaid,product_destinationid,product_stagediagramid ")
                '.Append("from l_products ")
                '.Append("where product_id = " & ProductId)

                '.Append("select p.product_id,p.product_name,p.product_type,p.product_indefinite,p.product_ukvisaid,p.product_destinationid,p.product_stagediagramid,coalesce(v.ukvisa_name,'None') as ukvisa_name,")
                ''.Append("p.product_weblink,Product_chargegroupid,Product_EventGroupID,Product_SLSID,Product_DTCategory, ")
                '.Append("p.Product_TargetGroupID,Product_CalcSet,product_mfbgroupid,product_webloopid,product_fasttrack ")
                '.Append(" B.ID as BranchID")
                '.Append("from l_products p left join l_ukvisa v on p.product_ukvisaid = v.ukvisa_id ")
                '.Append(" left join L_ProductTypes T on P.Type = T.ID ")
                '.Append(" Left join L_Branch B on T.Branch_ID")

                .Append("SELECT     p.Product_ID, p.Product_Name, p.Product_Type, p.Product_Indefinite, p.Product_UKVisaID, p.Product_DestinationID, p.Product_StageDiagramID, ")
                .Append("COALESCE (v.UKVisa_Name, 'None') AS ukvisa_name, p.Product_Weblink, p.Product_ChargeGroupID, p.Product_EventGroupID, p.Product_SLSID,  ")
                .Append("p.Product_DTCategory, p.Product_TargetGroupID, p.Product_CalcSet, p.Product_MFBGroupID, p.Product_WebLoopID, p.Product_FastTrack,  ")
                .Append("B.Branch_ID ")
                .Append("FROM dbo.L_Products AS p LEFT OUTER JOIN ")
                .Append("dbo.L_UKVisa AS v ON p.Product_UKVisaID = v.UKVisa_ID LEFT OUTER JOIN ")
                .Append("dbo.L_ProductTypes AS T ON p.Product_Type = T.ProductType_ID LEFT OUTER JOIN ")
                .Append("dbo.L_Branch AS B ON T.ProductType_BranchID = B.Branch_ID ")

                .Append(" where p.product_id = " & ProductId)
            End With

            myData = db.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then
                    Dim myRow As DataRow = myData.Rows(0)
                    Dim myProduct As New ProductInfo

                    With myProduct
                        .ProductId = myRow.Item("product_id")
                        .ProductName = IIf(IsDBNull(myRow.Item("product_name")), "", myRow.Item("product_name"))
                        .ProductTypeId = myRow.Item("product_type")
                        .IndefiniteLeave = IIf(myRow.Item("product_indefinite") = 1, True, False)
                        .UKVisaId = myRow.Item("product_ukvisaid")
                        .UKVisaName = IIf(IsDBNull(myRow.Item("ukvisa_name")), "", myRow.Item("ukvisa_name"))
                        .DestinationId = myRow.Item("product_destinationid")
                        .StageDiagramId = myRow.Item("product_stagediagramid")
                        .Weblink = IIf(IsDBNull(myRow.Item("product_weblink")), "", myRow.Item("product_weblink"))
                        .ChargeGroupId = myRow.Item("Product_chargegroupid")
                        .EventGroupId = myRow.Item("Product_EventGroupID")
                        .StockLetterSetId = myRow.Item("Product_SLSID")
                        .DateCategoryId = myRow.Item("Product_DTCategory")
                        .MFBGroupId = myRow.Item("Product_MFBGroupID")
                        .WebLoopId = myRow.Item("Product_WebLoopID")
                        .FastTrackId = myRow.Item("Product_FastTrack")
                        .TargetGroupId = myRow.Item("Product_TargetGroupID")
                        .CalcSetId = myRow.Item("Product_CalcSet")
                        .BranchID = IIf((myRow.Item("Branch_ID") Is DBNull.Value), 0, myRow.Item("Branch_ID"))
                    End With

                    myData.Dispose()

                    Return myProduct

                End If
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function GetAutomationGroups(ByVal ProductId As Integer, ByVal ProductTypeId As Integer) As Array

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim myData As DataTable
            Dim Links() As Long = Nothing
            Dim i As Integer
            Dim myRow As DataRow

            With SQL
                .Append("Select AutoGroupLink_GroupID from D_AutomationGroupLinks")
                .Append(" Where AutoGroupLink_ProductID =" & ProductId)
                .Append(" Or AutoGroupLink_ProductTypeID = " & ProductTypeId)
            End With

            myData = db.GetSQLDataTable(SQL.ToString)

            For Each myRow In myData.Rows
                ReDim Preserve Links(i)
                Links(i) = myRow.Item("autogrouplink_groupid")
                i += 1
            Next

            myData.Dispose()

            Return Links

        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function GetAutomationLinks(ByVal AutomationGroups() As Long, ByVal CompanyId As Long, ByVal ClientType As Integer, ByVal AccountType As Integer, ByVal StageLinkTo As Integer) As Array

        Dim db As New Global_Visas_Data_Layer.DataAccess

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim myData As DataTable
            Dim myRow As DataRow
            Dim Links() As Long = Nothing
            Dim i As Integer

            With SQL
                .Append("Select AutomationLink_AutomationID From D_AutomationLinks Where")

                .Append(" AutomationLink_GroupID in " & CommonFuncs.ArrayToCSV(AutomationGroups))
                .Append(" AND AutomationLink_StageID = " & StageLinkTo)
                If CompanyId = 0 Then
                    .Append(" AND AutomationLink_CType in (1,0)")
                Else
                    .Append(" AND AutomationLink_CType in (2,0)")
                End If

                If ClientType = 2 Then
                    .Append(" AND AutomationLink_SLA in (0," & AccountType & ")")
                End If

                'Group by automation ID to remove any duplicates
                .Append(" Group By AutomationLink_AutomationID")
            End With

            myData = db.GetSQLDataTable(SQL.ToString)

            For Each myRow In myData.Rows
                ReDim Preserve Links(i)
                Links(i) = myRow.Item("automationlink_automationid")
                i += 1
            Next

            myData.Dispose()

            Return Links
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function GetSLAProducts(ByVal CompanyId As Long, Optional ByVal ProductType As Integer = 0, Optional ByVal SLAOnly As Boolean = False) As DataTable

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL

                .Append(" SELECT     L_Products.Product_ID, max(L_Products.Product_Name) as Product,")
                .Append(" Case MAX(DefCharge_CompanyId)")
                .Append(" when 0 then sum(Case DefCharge_CompanyID when 0 then D_DefCharges.DefCharge_Amount else 0 end)")
                .Append(" else sum(Case DefCharge_CompanyID when 0 then 0 else D_DefCharges.DefCharge_Amount end) end as Amount,")
                .Append(" CAST (Case MAX(DefCharge_CompanyId)")
                .Append(" when 0 then sum(Case DefCharge_CompanyID when 0 then Defcharge_Amount + (defcharge_amount * defcharge_vat / 100) else 0 end)")
                .Append(" else sum(Case DefCharge_CompanyID when 0 then 0 else Defcharge_Amount + (defcharge_amount * defcharge_vat / 100) end) end as Money) as Gross,")
                .Append(" Case MAX(DefCharge_CompanyId)")
                .Append(" when 0 then sum(Case DefCharge_CompanyID when 0 then D_DefCharges.DefCharge_TPCost else 0 end)")
                .Append(" else sum(Case DefCharge_CompanyID when 0 then 0 else D_DefCharges.DefCharge_TPCost end) end as TPCost,")
                .Append(" Case MAX(DefCharge_CompanyId)")
                .Append(" when 0 then sum(Case DefCharge_CompanyID when 0 then Defcharge_amount - defcharge_tpcost else 0 end)")
                .Append(" else sum(Case DefCharge_CompanyID when 0 then 0 else Defcharge_amount - defcharge_tpcost end) end as Income,")
                .Append(" Case max(D_DefCharges.DefCharge_CompanyID) when 0 then '' else '   SLA' end as Type")
                .Append(" FROM         L_Products INNER JOIN ")
                .Append("D_DefCharges ON L_Products.Product_ID = D_DefCharges.DefCharge_ProductID")

                If ProductType > 0 Then
                    .Append(" WHERE Product_Type = " & ProductType)
                End If
                If SLAOnly Then 'Show Charges for this Company only
                    .Append(" And DefCharge_CompanyId in (" & CompanyId & ")")
                Else
                    .Append(" And DefCharge_CompanyId in (0," & CompanyId & ")")
                End If
                .Append(" Group BY L_Products.Product_ID")
                .Append(" Order BY L_Products.Product_ID")

            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function GetProductCharges(ByVal CompanyId As Long, ByVal ProductId As Integer, ByVal SLA As Boolean) As DataTable

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT DefCharge_ID, DefCharge_Type, ")
                .Append(" DefCharge_Amount as Net, cast (DefCharge_Amount + (Defcharge_Amount * (DefCharge_VAT / 100)) as Money) as Gross, DefCharge_TPCost AS TP, DefCharge_Amount - DefCharge_TPCost as Income, DefCharge_VAT as VAT ")
                .Append(" FROM D_DefCharges ")
                If SLA Then
                    .Append(" WHERE    (DefCharge_ProductID = " & ProductId & ") AND  (DefCharge_CompanyID in (" & CompanyId & "))")
                Else
                    .Append(" WHERE    (DefCharge_ProductID = " & ProductId & ") AND  (DefCharge_CompanyID in (0))")
                End If
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function GetMFBGroups() As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLDataTable("Select MFBGroup_ID, MFBGroup_Name from I_MFBGroups")
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function DeleteCharge(ByVal ChargeId As Integer) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.ExecuteSQL("delete from d_defcharges where defcharge_id = " & ChargeId)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function CreateDefaultCharge(ByVal ProductId As Integer, ByVal ProductName As String, ByVal ChargeTypeName As String, ByVal ConName As String, ByVal VAT As Decimal, ByVal Net As Decimal, ByVal TPCost As Decimal, ByVal ChargeTypeId As Integer, ByVal ChargeOrCredit As Integer, Optional ByVal CompanyId As Long = 0) As Long

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim TasksEvents As New TasksEvents
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim ChargeId As Long

            With SQL
                .Append("Insert Into D_DefCharges (DefCharge_ProductID, DefCharge_Type, DefCharge_EnteredBy, DefCharge_CompanyID, DefCharge_VAT, DefCharge_Amount, DefCharge_TPCost, DefCharge_Category)")
                .Append(" VALUES (" & ProductId & ",'" & cmn.SwapQuotes(ChargeTypeName.Trim) & "','" & cmn.SwapQuotes(ConName.Trim) & "'," & CompanyId & "," & VAT & "," & Net * ChargeOrCredit & "," & TPCost * ChargeOrCredit & ", " & ChargeTypeId & ")")
            End With
            ChargeId = db.ExecuteSQL(SQL.ToString, True)
            If ChargeId > 0 Then


                TasksEvents.GblFunc_Add_SysEvent("Admin (PM)", "Default Charge of type " & ChargeTypeName & " added for " & Net & " (Ex VAT) with TP cost of " & TPCost & " to product ID " & ProductId & " (" & ProductName.Trim & ")")

            End If

            Return ChargeId
        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
            TasksEvents = Nothing
            cmn = Nothing
        End Try
    End Function

    Public Shared Function GetProductName(ByVal ProductId As Long) As String
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLScalar("select product_name from l_products where product_id = " & ProductId)
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
           
        End Try
    End Function

    Public Shared Function ProductList(ByVal ProductType As Integer) As DataTable

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("SELECT [Product_ID],[Product_Name] FROM [GLOBAL].[dbo].[L_Products] WHERE PRODUCT_TYPE=" & ProductType)
            End With
            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function ProductList(ByVal ProductType As Integer, ByVal ProductID As Integer, ByVal ExcludeNIA As Boolean, ByVal BranchID As Integer) As DataTable

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim bWhere As Boolean = False

            With SQL
                .Append("SELECT Product_ID, max(Product_Name) as product_name, max(IsNull([ProductType_Name],'Not in Use')) AS Type, max(Case [Product_Indefinite] when 0 then '' else 'Ind' end) AS Ind, ")
                .Append(" Sum(Defcharge_Amount) as Net, cast(Sum(Defcharge_Amount + (defcharge_amount * defcharge_vat / 100)) as money)as Gross, Sum(Defcharge_tpcost) as TPCost, Sum(Defcharge_amount - defcharge_tpcost) as Income ")
                .Append(" FROM L_Products LEFT OUTER JOIN")
                .Append(" D_DefCharges ON L_Products.Product_ID = D_DefCharges.DefCharge_ProductID LEFT OUTER JOIN")
                .Append(" L_ProductTypes ON L_Products.Product_Type = L_ProductTypes.ProductType_ID")

                If ProductID <> -1 Then
                    .Append("Where Product_ID = " & CStr(ProductID) & " or ")
                    bWhere = True
                End If


                If ProductType > 0 Then
                    If bWhere = False Then
                        .Append(" WHERE Product_Type = " & ProductType)
                        bWhere = True
                    Else
                        .Append(" Product_Type = " & ProductType)
                    End If
                End If

                If ExcludeNIA Then

                    If ProductType > 0 Then
                        .Append(" AND")
                    Else
                        .Append(" WHERE")
                        bWhere = True
                    End If

                    .Append(" Product_Type > 0")
                End If

                If BranchID > -1 Then
                    If bWhere = True Then
                        .Append(" and ")
                    Else
                        .Append(" where ")
                    End If

                    .Append(" ProductTypes.BranchID = " & CStr(BranchID))
                End If


                .Append(" AND DefCharge_CompanyId = 0 ") 'non SLA filter
                .Append(" Group By Product_ID")
                .Append(" ORDER BY IsNull(max([producttype_name]),'zzz'), max(Product_name)")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function ProductTypeList(ByVal intBranchID As Integer, ByVal intProductType As Integer, ByVal bIncludeEveryone As Boolean) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim strSQL As String

            If intBranchID = -1 Then
                strSQL = "select producttype_id,producttype_name from l_producttypes "
            Else
                strSQL = "select producttype_id,producttype_name from l_producttypes where L_ProductTypes.ProductType_BranchID =" & CStr(intBranchID)
            End If

            If bIncludeEveryone = False Then
                If intProductType <> -1 Then
                    strSQL &= (" and (ProductType_dtDisabled is null or ProductType_ID = " & CStr(intProductType) & ") ")
                Else
                    strSQL &= (" and ProductType_dtDisabled is null")
                End If
            End If

            strSQL &= (" order by producttype_name")

            Return db.GetSQLDataTable(strSQL)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function ProductDefaultDocs(ByVal ProductId As Integer) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT DefDocReq_ID, DefDocReq_Name, DocReqType_Name, ")
                .Append(" Case DocReqType_clientType when 1 then 'Private' when 2 then 'Corporate' else '' end as Ctype, Case DocReqType_FTO when 1 then 'FTO' else '' end as FTO")
                .Append(" FROM D_DefDocReqs")
                .Append(" Left Outer Join L_DocReqTypes on D_DefDocReqs.DefDocReq_TypeID = L_DocReqTypes.DocReqType_ID")
                .Append(" WHERE DefDocReq_ProductID = " & ProductId)
                .Append(" ORDER BY docreqtype_Name, DefDocReq_Name")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function StageDiagramList() As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Try


            Return db.GetSQLDataTable("SELECT StageDiagram_ID,StageDiagram_Name FROM L_StageDiagram WHERE StageDiagram_ID <> 2")
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function ChargeGroupList() As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            'Return db.GetSQLDataTable("SELECT ChargeGroup_ID, ChargeGroup_Name FROM A_ChargeGroups WHERE (ChargeGroup_ID <> 2)")
            Return db.GetSQLDataTable("SELECT ChargeGroup_ID, ChargeGroup_Name FROM A_ChargeGroups")

        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function


    Public Shared Function SLSGroupList() As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Try
            Return db.GetSQLDataTable("SELECT SLS_ID, SLS_Name FROM I_SL_StockLetterSets ORDER BY SLS_Name")
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing

        End Try
    End Function

    Public Shared Function DateCategoryList() As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Try
            Return db.GetSQLDataTable("SELECT DTCat_ID, DTCat_Name FROM I_DTCat ORDER BY DTCat_Number")
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function CalculatorSetList() As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Try
            Return db.GetSQLDataTable("SELECT CalcSet_ID,CalcSet_Name FROM I_CalcSets ORDER BY CalcSet_ID")
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function GetProductTypeData(ByVal ProductTypeId As Integer) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT isnull(L_StageDiagram.StageDiagram_Name,'Not Set') as CaseStage, isnull(I_SL_StockLetterSets.SLS_Name,'Not Set') as SLS,")
                .Append(" isnull(A_ChargeGroups.ChargeGroup_Name,'Not Set') as ChargeGroup, isnull(I_EventGroups.EventGroup_Name,'Not Set') as EventGroup")
                .Append(" , isnull(I_dtcat.dtcat_Name,'Not Set') as DTCat")
                .Append(" , isnull(I_calcsets.Calcset_Name,'Not Set') as CalcSet")
                .Append(" , isnull(I_mfbgroups.MFBGroup_Name,'Not Set') as MFB")
                .Append(" , isnull(D_Webloops.Webloop_Name,'Not Set') as WebLoop")
                .Append(" , isnull(L_FastTrack.FastTrack_Name,'Not Set') as FastTrack")
                .Append(" , isnull(I_TargetGroups.TargetGroup_Name,'Not Set') as TargetGroup")
                .Append(" FROM L_ProductTypes LEFT OUTER JOIN")
                .Append(" I_EventGroups ON L_ProductTypes.ProductType_EventGroupID = I_EventGroups.EventGroup_ID LEFT OUTER JOIN")
                .Append(" A_ChargeGroups ON L_ProductTypes.ProductType_ChargeGroupID = A_ChargeGroups.ChargeGroup_ID LEFT OUTER JOIN")
                .Append(" I_SL_StockLetterSets ON L_ProductTypes.ProductType_SLSID = I_SL_StockLetterSets.SLS_ID LEFT OUTER JOIN")
                .Append(" L_StageDiagram ON L_ProductTypes.ProductType_StageDiagramID = L_StageDiagram.StageDiagram_ID LEFT OUTER JOIN")
                .Append(" I_DTCat ON L_ProductTypes.ProductType_DTCategory = I_DTCat.DTCat_ID LEFT OUTER JOIN")
                .Append(" I_CalcSets ON L_ProductTypes.ProductType_CalcSet = I_CalcSets.CalcSet_ID LEFT OUTER JOIN")
                .Append(" I_MFBGroups ON L_ProductTypes.ProductType_MFBGroupID = I_MFBGroups.MFBGroup_ID LEFT OUTER JOIN")
                .Append(" D_WebLoops ON L_ProductTypes.ProductType_WebLoopID = D_Webloops.Webloop_ID LEFT OUTER JOIN")
                .Append(" L_FastTrack ON L_ProductTypes.ProductType_FastTrack = L_FastTrack.FastTrack_ID LEFT OUTER JOIN")
                .Append(" I_TargetGroups ON L_ProductTypes.ProductType_TargetGroupID = I_TargetGroups.TargetGroup_ID")
                .Append(" WHERE L_ProductTypes.ProductType_ID = " & ProductTypeId)
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function ProductListByType(ByVal ProductType As Integer, ByVal ProductID As Integer) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("select product_id,product_name from l_products where product_type = " & ProductType)
                If ProductID = -1 Then
                    .Append(" and (Product_DTDisabled is null ) ")
                Else
                    .Append(" and (Product_ID = " & CStr(ProductID) & " or Product_DTDisabled is null ) ")
                End If
                .Append(" order by product_name")
                Return db.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function ProductTypesList() As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT [ProductType_ID],[ProductType_Name] as 'Product Type',[ProductType_DestinationID],[ProductType_DepartmentID],[ProductType_StageDiagramID]")
                .Append(",[ProductType_MFBGroupID],[ProductType_SLSID],[ProductType_ChargeGroupID],[ProductType_EventGroupID],[ProductType_WebLoopID],")
                .Append("[ProductType_FastTrack],[ProductType_DTCategory],[ProductType_CalcSet],[ProductType_TargetGroupID],[ProductType_BranchID],")
                .Append("[PRODUCTTYPE_DTDISABLED],[ProductType_ResidenceID] FROM [GLOBAL].[dbo].[L_ProductTypes]")
                Return db.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function ProductTypeName(ByVal caseId As Long) As String
        Dim prdctTypeName As String = String.Empty
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("select L_ProductTypes.ProductType_Name  from D_Case join L_Products on")
                .Append(" D_Case .Case_ProductID =L_Products .Product_ID join L_ProductTypes on")
                .Append(" L_Products .Product_Type = L_ProductTypes .ProductType_ID  where D_case.Case_ID =" & caseId)
            End With
            prdctTypeName = db.GetSQLScalar(SQL.ToString)
        Catch ex As Exception

        Finally
            db = Nothing
            SQL = Nothing
        End Try
        Return prdctTypeName
    End Function
End Class